﻿Imports Newtonsoft.Json

Public Class EmployeeInfoManager

    Private manager As SQLManager

    Public Sub New()
        manager = New SQLManager
    End Sub
    Public Function getAllEmployeesInfo() As List(Of EmployeeInfo)
        Dim empArr As New List(Of EmployeeInfo)
        Dim query As String = "SELECT * FROM tbl_employee_info"
        Dim resultString As String = "" + manager.executeQuery(query)

        empArr = JsonConvert.DeserializeObject(Of List(Of EmployeeInfo))(resultString)

        If (resultString = "") Then
            'MsgBox("NO DATA")
        End If




        Return empArr
    End Function

    Public Function retrieveEmployeeInfoByEmployeeId(ByVal id As Integer) As EmployeeInfo
        Dim empInfoArr As New List(Of EmployeeInfo)
        Dim empInfo As EmployeeInfo
        Dim query As String = "SELECT * FROM `tbl_employee_info` WHERE employee_id='" + id.ToString + "'"
        Dim resultString As String = "" + manager.executeQuery(query)

        empInfoArr = JsonConvert.DeserializeObject(Of List(Of EmployeeInfo))(resultString)

        If (resultString = "") Then
            MsgBox("NO DATA")
        End If

        For Each tempEmp As EmployeeInfo In empInfoArr
            empInfo = tempEmp
            Exit For
        Next


        Return empInfo
    End Function

    Public Function insertEmployeeInfo(ByVal empInfoObj As EmployeeInfo)

        Dim query As String = "INSERT INTO `payroll`.`tbl_employee_info` (`id`, `employee_id`, `employee_no`, `last_name`, `first_name`, `middle_name`, `no_sss`, `no_philhealth`, `no_pagibig`, `no_tin`, `tax_code`, `designation_id`, `date_hired`, `end_contract`, `date_resigned`) VALUES (NULL, '" + empInfoObj.employee_id.ToString + "', '" + empInfoObj.employee_no.ToString + "', '" + empInfoObj.last_name + "', '" + empInfoObj.first_name + "', '" + empInfoObj.middle_name + "', '" + empInfoObj.no_sss + "', '" + empInfoObj.no_philhealth + "', '" + empInfoObj.no_pagibig + "', '" + empInfoObj.no_tin + "', '" + empInfoObj.tax_code + "', '" + empInfoObj.designation_id.ToString + "', '" + empInfoObj.date_hired + "', '" + empInfoObj.end_contract + "', '" + empInfoObj.date_resigned + "')"
        manager.executeUpdate(query)
    End Function

    Public Function updateEmployeeInfo(ByVal empInfoObj As EmployeeInfo)
        Dim query As String = String.Format("UPDATE `payroll`.`tbl_employee_info` SET `employee_id` = '{0}', `employee_no` = '{1}', `last_name` = '{2}', `first_name` = '{3}', `middle_name` = '{4}', `no_sss` = '{5}', `no_philhealth` = '{6}', `no_pagibig` = '{7}', `no_tin` = '{8}', `tax_code` = '{9}', `designation_id` = '{10}', `date_hired` = '{11}', `end_contract` = '{12}', `date_resigned` = '{13}' WHERE `tbl_employee_info`.`id` = '{14}'", empInfoObj.employee_id, empInfoObj.employee_no, empInfoObj.last_name, empInfoObj.first_name, empInfoObj.middle_name, empInfoObj.no_sss, empInfoObj.no_philhealth, empInfoObj.no_pagibig, empInfoObj.no_tin, empInfoObj.tax_code, empInfoObj.designation_id.ToString, empInfoObj.date_hired, empInfoObj.end_contract, empInfoObj.date_resigned, empInfoObj.id)
        manager.executeUpdate(query)
    End Function

    Public Function retrieveEmployeeInfoById(ByVal id As Integer) As EmployeeInfo
        Dim empInfoArr As New List(Of EmployeeInfo)
        Dim empInfo As New EmployeeInfo
        Dim query As String = "SELECT * FROM `tbl_employee_info` WHERE id='" + id.ToString + "'"
        Dim resultString As String = "" + manager.executeQuery(query)

        empInfoArr = JsonConvert.DeserializeObject(Of List(Of EmployeeInfo))(resultString)

        

        For Each tempEmp As EmployeeInfo In empInfoArr
            empInfo = tempEmp
            Exit For
        Next


        Return empInfo
    End Function

    Public Function deleteEmpInfoByEmployeeID(ByVal empID As Integer) As Boolean

        Dim query As String = "DELETE FROM `tbl_employee_info` WHERE employee_id='" + empID.ToString + "'"
        manager.executeQuery(query)


        Return True
    End Function

    Public Function retrieveEmployeeInfoArrayByName(ByVal name As String) As List(Of EmployeeInfo)
        Dim empInfoArr As List(Of EmployeeInfo)
        Dim empInfo As New EmployeeInfo
        Dim query As String = "SELECT * FROM `tbl_employee_info` WHERE last_name LIKE '%" + name + "%' OR first_name LIKE '%" + name + "%' OR middle_name LIKE '%" + name + "%'"
        Dim resultString As String = "" + manager.executeQuery(query)

        empInfoArr = JsonConvert.DeserializeObject(Of List(Of EmployeeInfo))(resultString)





        Return empInfoArr
    End Function


    Public Function retrieveEmployeeInfoArrayByEmployeeNo(ByVal no As String) As List(Of EmployeeInfo)
        Dim empInfoArr As List(Of EmployeeInfo)
        Dim empInfo As New EmployeeInfo
        Dim query As String = "SELECT * FROM `tbl_employee_info` WHERE employee_no='" + no.ToString + "'"
        Dim resultString As String = "" + manager.executeQuery(query)

        empInfoArr = JsonConvert.DeserializeObject(Of List(Of EmployeeInfo))(resultString)






        Return empInfoArr
    End Function




End Class
